# gozerbot/databse/alchemy.py
#
#

""" alchemy interface. """

__status__ = "seen"

## gozerbot imports

from gozerbot.utils.log import rlog

## sqlalchemy imports

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.orderinglist import ordering_list
from sqlalchemy import Text, Integer, Sequence, ForeignKey, DateTime, Column, String, Table
from sqlalchemy.orm import relation

## basic imports

import sqlalchemy
import os
import time
import logging
from datetime import datetime

## defines

created = []

## create_all function

def create_all(plugname='all', base=None):
    rlog(10, 'alchemy', 'running create_all (%s)' % plugname)
    if plugname not in created:
        created.append(plugname)
        if not base: base = Base
        try: base.metadata.create_all()
        except Exception, ex: rlog(10, 'alchemy', 'problem creating %s tables: %s' % (str(base), str(ex)))
    else: rlog(10, 'alchemy', '%s tables already created' % plugname)

# defines 

Base = declarative_base()

## USER MODEL

user = Table('user', Base.metadata,
    Column('name', String(255), primary_key=True)
)

email = Table('email', Base.metadata,
    Column('name', String(255), ForeignKey(user.c.name), nullable=False),
    Column('email', String(255), nullable=False),
    Column('order', Integer, nullable=False)
)

class User(Base):
    __table__ = user
    _userhosts = relation("UserHost", backref="user", cascade="all, delete-orphan")
    _perms = relation("Perms", backref="user", cascade="all, delete-orphan")
    _permits = relation("Permits", backref="user",cascade="all, delete-orphan" )
    _statuses = relation("Statuses", backref="user", cascade="all, delete-orphan")
    _pasword = relation("Passwords", backref="user", cascade="all, delete-orphan")
    _email = relation("Email", backref="user", collection_class=ordering_list('order'),
                        cascade="all, delete-orphan", order_by=[email.c.order])
    email = association_proxy('_email', 'email')
    userhosts = association_proxy('_userhosts', 'userhost')
    perms = association_proxy('_perms', 'perm')
    permits = association_proxy('_permits', 'permit')
    statuses = association_proxy('_statuses', 'status')
    password = association_proxy('_password', 'passwd')

class Email(Base):
    __table__ = email
    __mapper_args__ = {'primary_key':[email.c.name,email.c.email]}

    def __init__(self, email):
        self.email = email

class UserHost(Base):
    __tablename__ = 'userhosts'
    userhost = Column('userhost', String(255), primary_key=True)
    name = Column('name', String(255), ForeignKey('user.name'), nullable=False)

    def __init__(self, userhost):
        self.userhost = userhost

class Perms(Base):
    __tablename__ = 'perms'
    name = Column('name', String(255), ForeignKey('user.name'), nullable=False)
    perm = Column('perm', String(255), nullable=False)
    __mapper_args__ = {'primary_key':[name,perm]}

    def __init__(self, perm):
        self.perm = perm

class Permits(Base):
    __tablename__ = 'permits'
    name = Column('name', String(255), ForeignKey('user.name'), nullable=False)
    permit = Column('permit', String(255), nullable=False)
    __mapper_args__ = {'primary_key':[name,permit]}

    def __init__(self, permit):
        self.permit = permit

class Statuses(Base):    
    __tablename__ = 'statuses'
    name = Column('name', String(255), ForeignKey('user.name'), nullable=False)
    status = Column('status', String(255), nullable=False)
    __mapper_args__ = {'primary_key':[name,status]}

    def __init__(self, status):
        self.status = status

class Passwords(Base):    
    __tablename__ = 'passwords'
    name = Column('name', String(255), ForeignKey('user.name'), primary_key=True)
    passwd = Column('passwd', String(255), nullable=False)

    def __init__(self, passwd):
        self.passwd = passwd

## Birthday Model

class Birthday(Base):
    __tablename__ = 'birthday'
    __table_args__ = {'useexisting': True}
    name = Column('name', String(255), primary_key=True)
    birthday = Column('birthday', String(255), nullable=False)

    def __init__(self, name, birthday):
        self.name = name
        self.birthday = birthday

## Quote Model

class Quotes(Base):
    __tablename__ = 'quotes'
    __table_args__ = {'useexisting': True }
    indx = Column('indx', Integer, Sequence('quotes_indx_seq', optional=True), primary_key=True)
    quote = Column('quote', Text, nullable=False)
    userhost = Column('userhost', String(255), ForeignKey('userhosts.userhost'), nullable=False)
    createtime = Column('createtime', DateTime, nullable=False)
    nick = Column('nick', String(255), nullable=False)

    def __init__(self, quote, userhost, createtime, nick):
        self.quote = quote
        self.userhost = userhost
        self.createtime = createtime
        self.nick = nick

## Infoitems Model

class InfoItems(Base):
    __tablename__ = 'infoitems'
    __table_args__ = {'useexisting': True}
    indx = Column('indx', Integer, Sequence('infoitems_indx_seq', optional=True), primary_key=True)
    item = Column('item', String(255), nullable=False)
    description = Column('description', Text, nullable=False)
    userhost = Column('userhost', String(255), ForeignKey('userhosts.userhost'), nullable=False)
    time = Column('time', DateTime, nullable=False)

    def __init__(self, item, description, userhost, ttime=None):
        self.time = ttime and ttime or datetime.now()
        self.item = item.lower()
        self.description = description
        self.userhost = userhost


## Karma Model

class Karma(Base):
    __tablename__ = 'karma'
    __table_args__ = {'useexisting': True}
    item = Column('item', String(255), primary_key=True)
    value = Column('value', Integer, nullable=False)

    def __init__(self, item, value):  
        self.item = item
        self.value = value

class WhyKarma(Base):
    __tablename__ = 'whykarma'
    __table_args__ = {'useexisting': True}
    item = Column('item', String(255), nullable=False)
    updown = Column('updown', String(10), nullable=False)
    why = Column('why', Text, nullable=False)
    __mapper_args__ = {'primary_key':[item,updown,why]}

    def __init__(self, item, updown, why):
        self.item = item
        self.updown = updown
        self.why = why

class WhoKarma(Base):
    __tablename__ = 'whokarma'
    __table_args__ = {'useexisting': True}
    item = Column('item', String(255), nullable=False)
    nick = Column('nick', String(255), nullable=False)
    updown = Column('updown', String(10), nullable=False)
    __mapper_args__ = {'primary_key': [item, nick, updown]}

    def __init__(self, item, nick, updown):
        self.item = item
        self.nick = nick
        self.updown = updown


## Todo Model

class Todo(Base):
    __tablename__ = 'todo'
    __table_args__ = {'useexisting': True}
    indx = Column('indx', Integer, Sequence('todo_indx_seq', optional=True), primary_key=True)
    name = Column('name', String(255), nullable=False)
    time = Column('time', DateTime)
    duration = Column('duration', Integer)
    warnsec = Column('warnsec', Integer)
    descr = Column('descr', Text, nullable=False)
    priority = Column('priority', Integer)

    def __init__(self, name, time, duration, warnsec, descr, priority):
        self.name = name
        self.time = time
        self.duration = duration
        self.warnsec = warnsec
        self.descr = descr
        self.priority = priority

## Lists Model

class Lists(Base):
    __tablename__ = 'list'
    __table_args__ = {'useexisting': True}
    indx = Column('indx', Integer, Sequence('list_indx_seq', optional=True), primary_key=True)
    username = Column('username', String(255), nullable=False)
    listname = Column('listname', String(255), nullable=False)
    item = Column('item', Text, nullable=False)

    def __init__(self, username, listname, item):
        self.username = username
        self.listname = listname
        self.item = item

## IRCLogs Model

class ChatLog(Base):
    __tablename__ = 'chatlog'
    __table_args__ = {'useexisting': True}
    id = Column('id', Integer, primary_key=True)
    time = Column('time', DateTime, nullable=False, default=datetime.now)
    network = Column('network', String(256), nullable=False, default='') 
    target = Column('target', String(256), nullable=False, default='')   
    nick = Column('nick', String(256), nullable=False, default='')
    type = Column('type', String(256), nullable=False, default='')
    message = Column('message', Text, nullable=False, default='') 

    def __init__(self, **kwargs):
        for k, v in kwargs.items():
            setattr(self, k, v)
